package Actions;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;
import java.util.*; 
import support.*;
import org.apache.struts.action.*;

public class specAnaAction extends Action 
{
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response){
			
		forms.adminAnaform ar = (forms.adminAnaform) form;
		
		String WhichPage = "Spec";		
		ar.setWhichpage(WhichPage);
		
		request.getSession().setAttribute("WhichPage", WhichPage);

		String Spec = "";
		String url = "jdbc:postgresql://localhost/Applications";
		Connection conn = null;
		Statement stmt = null, stmt2 = null;
		PreparedStatement ps = null;
		ResultSet rs = null, rs2 = null;
		String instruc = null;
		
		try {
			// Registering Postgresql JDBC driver
			Class.forName("org.postgresql.Driver");
			// Open a connection to the database	
			conn = DriverManager.getConnection(
				"jdbc:postgresql://localhost:5432/Applications", "postgres",
				"postgres");
			// Create the statement
		    stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
		                				ResultSet.CONCUR_UPDATABLE);
		    stmt2 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
										ResultSet.CONCUR_UPDATABLE);
			
			//output all the specializations

			String special = null;
			
			rs2 = stmt2.executeQuery("SELECT c.SpecID, COUNT(c.SpecID) AS numS FROM " +
					"(SELECT DISTINCT NAMEID, SpecID FROM MainTable) AS c GROUP BY c.SpecID");

			while( rs2.next() )
			{	
				//which Specialization to show on the application page
				int current = rs2.getInt("SpecID");
				System.out.println("fdafs:"+ current);
				rs = stmt.executeQuery("SELECT * FROM Specializations WHERE ID = " + current );
				while( rs.next() )
				{
					
					special = rs.getString("Specialization");
					int count = rs2.getInt("numS");
					
					ar.updateSpecs(new forms.displayform(special,count));
				}
			}
			if(rs != null)
		    	rs.close();
			    rs2.close();
			    stmt.close();
			    stmt2.close();
			    conn.close();	
		} catch (SQLException e) 
		  { 
			System.out.println(e);
		  } catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			
		return mapping.findForward("specAnaSuccess");
	}
}
